package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import control.ConnectionManage;
import db.parking.beans.Orders;
import db.parking.beans.QuarterReports;

/**
 * AdminReports manages all requests From,To table Reports
 * @author Operations
 *
 */
public class AdminReports {
	

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();
	
	
	/**
	 * Functions insert a new Quarter report into DB table reports
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean insertQuarterReports(QuarterReports bean) throws Exception {
		String sql = "INSERT quarterreports  (lotNo , reportname, year, quarter) "
				+ "VALUES (?, ?, ?, ?)";
		ResultSet keys = null;
		
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
			stmt.setInt(1, bean.getLotNo());
			stmt.setString(2, bean.getReportName());
			stmt.setInt(3, bean.getYear());
			stmt.setInt(4, bean.getQuarter());
			
			int affected = stmt.executeUpdate();
			if (affected == 1) {
				return true;
			} else {
				System.err.println("No rows affected");
				return false;
			}

		} catch (SQLException e ) {
			System.err.println(e + "  AdminReports->insert");
			return false;
		} finally {
			if (keys != null)
				keys.close();
		}
	}
	
	/**
	 * Functions returns all Quarter Reports to fill the tables with
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static QuarterReports getReport(QuarterReports bean) throws Exception {
	
		String sql = "SELECT * reports  (lotNo , reportname, year, quarter) "
				+ "VALUES (?, ?, ?, ?)";
		ResultSet rs = null;
		
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
			stmt.setInt(1, bean.getLotNo());
			stmt.setString(2, bean.getReportName());
			stmt.setInt(3, bean.getYear());
			stmt.setInt(4, bean.getQuarter());
			
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				QuarterReports report = new QuarterReports();
				report.setLotNo(rs.getInt("lotNo"));
				report.setReportName(rs.getString("reportname"));
				report.setYear(rs.getInt("year"));
				report.setQuarter(rs.getInt("quarter"));
				
				return report;
			} else {
				System.err.println("No rows affected");
				return null;
			}

		} catch (SQLException e ) {
			System.err.println(e + "  AdminReports->getReport");
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}	
	
	

}